﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class JobApplicationDB 
    {
        // You can add custom code to this file. Changes will not be overwritten.
        // 
        // If you want Entity Framework to drop and regenerate your database
        // automatically whenever you change your model schema, add the following
        // code to the Application_Start method in your Global.asax file.
        // Note: this will destroy and re-create your database with every model change.
        // 
        // System.Data.Entity.Database.SetInitializer(new System.Data.Entity.DropCreateDatabaseIfModelChanges<Pr0ject0.Models.JobApplicationDB>());

        private string connection;
        private List<JobApplicationModel> applications;
        private AvailableHoursDB hoursDb;
        private PreviousJobDB previousJobsDb;
        private ReferenceDB referencesDb;
        private EducationDB educationDb;
        private QuestionAnswerDb qaDb;

        public List<JobApplicationModel> jobApplications
        {
            get { return new List<JobApplicationModel>(applications); }
        }

        private volatile static JobApplicationDB singleton;
        private static readonly object lockObj = new object();

        public static JobApplicationDB GetInstance()
        {
            if (singleton == null)
            {
                lock (lockObj)
                {
                    if (singleton == null) singleton = new JobApplicationDB();
                }
            }

            return singleton;
        }

        public static JobApplicationDB GetInstance(string connectionString)
        {

            if (singleton == null)
            {
                lock (lockObj)
                {
                    if (singleton == null) singleton = new JobApplicationDB(connectionString);
                }
            }

            return singleton;
        }

        private JobApplicationDB()
        {
            connection = "DB";
            hoursDb = new AvailableHoursDB();
            previousJobsDb = new PreviousJobDB();
            referencesDb = new ReferenceDB();
            educationDb = new EducationDB();
            qaDb = new QuestionAnswerDb(); 
            update();
        }

        private JobApplicationDB(string connectionString)
        {
            connection = connectionString;
            hoursDb = new AvailableHoursDB(connectionString);
            previousJobsDb = new PreviousJobDB(connectionString);
            referencesDb = new ReferenceDB(connectionString);
            educationDb = new EducationDB(connectionString);
            qaDb = new QuestionAnswerDb(connectionString);
            update();
        }

        public void update()
        {
            previousJobsDb.update();
            hoursDb.update();
            educationDb.update();
            referencesDb.update();
            qaDb.update();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand appCmd = new SqlCommand("SELECT * FROM [JobApplicationModels]", conn);
            conn.Open();
            SqlDataReader reader = appCmd.ExecuteReader();

            applications = new List<JobApplicationModel>();

            while (reader.Read())
            {
                JobApplicationModel model = new JobApplicationModel();
                List<PreviousJobModel> previousJobs = new List<PreviousJobModel>();
                List<ReferenceModel> references = new List<ReferenceModel>();
                List<EducationModel> education = new List<EducationModel>();
                List<AnswerModel> answers;

                //get primitive model fields
                model.ID = Convert.ToInt32(reader["AppID"].ToString());
                model.first = reader["first"].ToString();
                model.last = reader["last"].ToString();
                model.middle = reader["middle"].ToString();
                model.address1 = reader["address1"].ToString();
                model.address2 = reader["address2"].ToString();
                model.city = reader["city"].ToString();
                model.state = reader["state"].ToString();
                model.zip = Convert.ToInt32(reader["zip"].ToString());
                model.phone = Convert.ToInt64(reader["phone"].ToString());
                model.jobID = Convert.ToInt32(reader["JobID"].ToString());
                model.status = Convert.ToInt32(reader["Status"].ToString());

                //get model class fields
                foreach (PreviousJobModel prev in previousJobsDb.PreviousJobsList)
                {
                    if (prev.ID == model.ID)
                    {
                        previousJobs.Add(prev);
                    }
                }

                model.previousJobs = previousJobs;

                foreach (ReferenceModel refer in referencesDb.ReferenceList)
                {
                    if(refer.AppId == model.ID)
                    {
                        references.Add(refer);
                    }
                }

                model.references = references;

                foreach (EducationModel edu in educationDb.EducationList)
                {
                    if (edu.ID == model.ID)
                    {
                        education.Add(edu);
                    }
                }

                model.education = education;

                foreach (AvailableHoursModel hrs in hoursDb.AvailableHoursList)
                {
                    if (hrs.ID == model.ID)
                    {
                        model.hours = hrs;
                    }
                }
                model.answers = qaDb.GetAnswers(model.ID);
                applications.Add(model);

                 
            }
            reader.Close();
            conn.Close();
        }

        public void Add(List<JobApplicationModel> list)
        {
            foreach (JobApplicationModel model in list)
            {
                Add(model);
            }
            
        }

        public void Add(JobApplicationModel model)
        {
            int newPK = 0;
            int rows;
            string insert;
            Object[] objects;
            SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand insertCmd;
            SqlCommand maxIdCmd = new SqlCommand("SELECT MAX(AppID) FROM JobApplicationModels", conn1);
            SqlDataReader max;
            
            conn1.Open();
            max = maxIdCmd.ExecuteReader();
            
            if(max.Read())
            {
                try
                {
                    newPK = max.GetInt32(0) + 1;
                }
                catch (Exception e)
                {
                }
            }
            max.Close();
            conn1.Close();

            model.cascadeIDSet(newPK);

            objects = new Object[]{model.ID, model.first, model.last, model.middle, model.address1, model.address2, 
                                   model.city, model.state, model.zip, model.phone, model.jobID, model.status};

            insert = string.Format(
                "INSERT INTO JobApplicationModels VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}, {9}, {10}, 3)",
                objects);
            insertCmd = new SqlCommand(insert, conn2);

            conn2.Open();
            insertCmd.ExecuteNonQuery();
            conn2.Close();

            hoursDb.Add(model.hours);
            educationDb.Add(model.education);
            previousJobsDb.Add(model.previousJobs);
            referencesDb.Add(model.references);
            qaDb.AddAnswers(model.answers);
            
            update(); 
        }

        public List<JobApplicationModel> GetStatusList(int status)
        {
            return applications.FindAll(x => x.status == status);  
        }

        public void ChangeStatus(string userID, int appID, int newStatus)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());

            SqlCommand cmd = new SqlCommand(string.Format("Update JobApplicationModels Set status = {0} Where appID = {1}", newStatus, appID), conn);

            conn.Open();

            cmd.ExecuteNonQuery();

            conn.Close();
            update();
        }

        public void ChangeReferenceStatus(int refId, bool newStatus)
        {
            referencesDb.ChangeReferenceStatus(refId, newStatus);
        }

    }
}
